#include "sql.h"
#include <QSqlDatabase>
#include <QDebug>
#include <QCoreApplication>
#include <QtSql>

Sql *Sql::ptrSql = nullptr;

Sql::Sql(QObject *parent) : QObject(parent)
{

}

//初始化数据库
void Sql::init()
{
    if (QSqlDatabase::drivers().isEmpty())
    {
        qDebug() << "No database drivers found!";
    }

    if (QSqlDatabase::contains("ebookTool"))
    {
        m_db = QSqlDatabase::database("ebookTool");
    }
    else
    {
        m_db = QSqlDatabase::addDatabase("QSQLITE","ebookTool");
    }

#if 0
    QString dbPath = QCoreApplication::applicationDirPath()+"/ebookTool.db";
    qDebug() << "dbPath=" << dbPath;
#endif

    QString dbPath = "D:\\Software\\QT\\File\\EBookToolNoReg\\EBookTool\\EBookTool\\ebookTool.db";

    m_db.setDatabaseName(dbPath);
    bool open = m_db.open();
    QSqlQuery q(m_db);
    QSqlError lastErr = q.lastError();

    if (!open)
    {
        qDebug() << "Unable to open database! Err:" << lastErr;
    }
    else
    {
        qDebug() << "database open success.";
    }
}

bool Sql::addBookinfo(QList<EbookInfo> bookInfoList)
{
    QSqlQuery sql(m_db);
    bool isSuccess = true;

    m_db.transaction(); //开启事务，好像开启事务后其中的调试输出没作用了
    for(EbookInfo bookInfo: bookInfoList)
    {
        bool isExist = false;
        QString strSearch = QString("select bookpath from ebook where bookpath='%1';").arg(bookInfo.bookpath);
        sql.exec(strSearch);
        if (sql.first() && sql.value(0).toString() == bookInfo.bookpath) //判断是否已经存在于数据库中了
        {
            isExist = true;
        }

        if (!isExist)
        {
            QString strSql = QString("insert into ebook values (NULL,'%1','%2','%3','%4','%5','%6','%7','%8','%9','%10');").
                    arg(bookInfo.bookname).
                    arg(bookInfo.booktype).
                    arg(bookInfo.author).
                    arg(bookInfo.publisher).
                    arg(bookInfo.publishyear).
                    arg(bookInfo.bookpath).
                    arg(bookInfo.bookdir).
                    arg(bookInfo.bookisbn).
                    arg(bookInfo.booklabel).
                    arg(bookInfo.bookintroduction);
            sql.exec(strSql);
            QSqlError err = sql.lastError();
            if (err.type() != QSqlError::NoError)
            {
                qDebug() << "insert into ebook err:"<<err.text();
                isSuccess = false;
            }
        }
    }
    m_db.commit();
    return isSuccess;
}

bool Sql::addBookDir2db(QVector<QString> dirVec)
{
    QSqlQuery sql(m_db);
    bool isSuccess = true;

    m_db.transaction(); //开启事务
    for(QString dir: dirVec)
    {
        bool isExist = false;
        QString strSearch = QString("select dirpath from ebookdir where dirpath='%1';").arg(dir);
        sql.exec(strSearch);
        if (sql.first() && sql.value(0).toString() == dir) //判断是否已经存在于数据库中了，是不是可以先全删了在添加？
        {
            isExist = true;
        }

        if (!isExist)
        {
            QString strSql = QString("insert into ebookdir values ('%1');").arg(dir);
            sql.exec(strSql);
            QSqlError err = sql.lastError();
            if (err.type() != QSqlError::NoError)
            {
                qDebug() << "insert into ebook err:"<<err.text();
                isSuccess = false;
            }
        }
    }
    m_db.commit();
    return isSuccess;
}

bool Sql::delBookDir(QString delDir)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from ebookdir where dirpath = '%1';").arg(delDir));
}

bool Sql::clearBookDir()
{
    QSqlQuery sql(m_db);
    return sql.exec("delete from ebookdir;");
}

QVector<QString> Sql::getBookDir()
{
    QVector<QString> dirVec;
    QSqlQuery sql(m_db);
    sql.exec(QString("select dirpath from ebookdir;"));
    while(sql.next())
    {
        dirVec.push_back(sql.value(0).toString());
    }
    return dirVec;
}

//添加选中的文件类型到数据库，如pdf,txt,epub等
bool Sql::addBooktype2db(QStringList booktypeList)
{
    QSqlQuery sql(m_db);
    sql.exec("delete from ebooktype;");
    if (booktypeList.size() > 0)
    {
        for (QString booktype : booktypeList)
        {
            sql.exec(QString("insert into ebooktype values ('%1');").arg(booktype));
            QSqlError err = sql.lastError();
            if (err.type() != QSqlError::NoError)
            {
                qDebug() << "insert into ebook err:"<<err.text();
                return false;
            }
        }
    }
    return true; //booktypeList为空也返回true
}

bool Sql::clearBooktype()
{
    QSqlQuery sql(m_db);
    return sql.exec("delete from ebooktype;");
}

QStringList Sql::getBooktype()
{
    QSqlQuery sql(m_db);
    QStringList booktypeList;
    sql.exec("select * from ebooktype;");
    while(sql.next())
    {
        booktypeList.append(sql.value(0).toString());
    }
    return booktypeList;
}

QList<EbookInfo> Sql::getBookinfo()
{
    QSqlQuery sql(m_db);
    QList<EbookInfo> bookInfoList;
    EbookInfo bookInfo;
    sql.exec(QString("select * from ebook;"));
    while (sql.next())
    {
        bookInfo.bookname = sql.value(1).toString();
        bookInfo.booktype = sql.value(2).toString();
        bookInfo.author = sql.value(3).toString();
        bookInfo.publisher = sql.value(4).toString();
        bookInfo.publishyear = sql.value(5).toString();
        bookInfo.bookpath = sql.value(6).toString();
        bookInfo.bookdir = sql.value(7).toString();
        bookInfo.bookisbn = sql.value(8).toString();
        bookInfo.booklabel = sql.value(9).toString();
        bookInfo.bookintroduction = sql.value(10).toString();
        bookInfoList.append(bookInfo);
    }
    return bookInfoList;
}

QList<EbookInfo> Sql::getBookinfoBasisToType(QString type, QString searchItem)
{
    QSqlQuery sql(m_db);
    QList<EbookInfo> bookInfoList;
    EbookInfo bookInfo;
    QString sqlStr;
    if ("booklabel"==type)
    {
        //booklabel需要模糊查询，如select * from ebook where booklabel like '%C++%';
        sqlStr = QString("select * from ebook where %1 like '%%2%';").arg(type).arg(searchItem);
    }
    else
    {
        //如select * from ebook where booktype = 'pdf';
        sqlStr = QString("select * from ebook where %1 ='%2';").arg(type).arg(searchItem);
    }
    sql.exec(sqlStr);
    while(sql.next())
    {
        bookInfo.bookname = sql.value(1).toString();
        bookInfo.booktype = sql.value(2).toString();
        bookInfo.author = sql.value(3).toString();
        bookInfo.publisher = sql.value(4).toString();
        bookInfo.publishyear = sql.value(5).toString();
        bookInfo.bookpath = sql.value(6).toString();
        bookInfo.bookdir = sql.value(7).toString();
        bookInfo.bookisbn = sql.value(8).toString();
        bookInfo.booklabel = sql.value(9).toString();
        bookInfo.bookintroduction = sql.value(10).toString();
        bookInfoList.append(bookInfo);
    }
    return bookInfoList;
}

//获取书籍总数
quint32 Sql::getAllBookNum()
{
    QSqlQuery sql(m_db);
    quint32 num = 0;
    sql.exec("select count(id) from ebook;");
    while(sql.next())
    {
        num = sql.value(0).toUInt();
    }
    qDebug() << "allbooknum=" << num;
    return num;
}

//根据类型查询书籍数量
quint32 Sql::getBookCount(QString type, QString item)
{
    QSqlQuery sql(m_db);
    quint32 count = 0;
    QString sqlStr;
    if ("booklabel"==type)
    {
        //booklabel需要模糊查询，如select count(id) from ebook where booklabel like '%C++%';
        sqlStr = QString("select count(id) from ebook where %1 like '%%2%';").arg(type).arg(item);
    }
    else
    {
        //如select count(id) from ebook where booktype = 'pdf';
        sqlStr = QString("select count(id) from ebook where %1 ='%2';").arg(type).arg(item);
    }

    sql.exec(sqlStr);
    while(sql.next())
    {
        count = sql.value(0).toUInt();
    }

    //qDebug() << "sqlStr=" << sqlStr;
    //qDebug() << "type=" << type << ",item="<<item <<",count=" << count;
    return count;
}

QStringList Sql::getBookTypeList()
{
    QList<QString> bookTypeList;
    QSqlQuery sql(m_db);
    sql.exec("select booktype from ebook;");
    while(sql.next())
    {
        QString typeStr = sql.value(0).toString();
        if (!bookTypeList.contains(typeStr))
        {
            bookTypeList.append(typeStr);
            //qDebug() << "booktype=" << typeStr;
        }
    }
    return bookTypeList;
}

QStringList Sql::getBookDirList()
{
    QList<QString> bookDirList;
    QSqlQuery sql(m_db);
    sql.exec("select bookdir from ebook;");
    while(sql.next())
    {
        QString dirStr = sql.value(0).toString();
        if (!bookDirList.contains(dirStr))
        {
            bookDirList.append(dirStr);
            //qDebug() << "bookdir=" << dirStr;
        }
    }
    return bookDirList;
}

QStringList Sql::getBookLabelList()
{
    QList<QString> bookLabelList;
    QSqlQuery sql(m_db);
    sql.exec("select booklabel from ebook;");
    while(sql.next())
    {
        QString labelStr = sql.value(0).toString();
        QStringList labelList = labelStr.split(";");
        for (QString label : labelList)
        {
            if (label.length()>0 && !bookLabelList.contains(label))
            {
                bookLabelList.append(label);
            }
        }
    }

//    for (QString label : bookLabelList)
//    {
//        qDebug() << "====>>label="<<label;
//    }

    return bookLabelList;
}

QString Sql::getBookIntroduction(QString bookpath)
{
    QString bookIntroduction;
    QSqlQuery sql(m_db);
    sql.exec(QString("select bookintroduction from ebook where bookpath='%1';").arg(bookpath));
    while(sql.next())
    {
        bookIntroduction = sql.value(0).toString();
    }
    return bookIntroduction;
}

bool Sql::updateBookInfoBasisToType(QString type, QString item, QString bookpath)
{
    QSqlQuery sql(m_db);
    //如update ebook set booklabel='C++' where bookpath='D:/xxx.pdf';
    sql.exec(QString("update ebook set %1 = '%2' where bookpath='%3';").
             arg(type).arg(item).
             arg(bookpath));
    QSqlError err = sql.lastError();
    if (err.type() != QSqlError::NoError)
    {
        qDebug() << "update ebook err:"<<err.text();
        return false;
    }
    else
    {
        qDebug() << "update ebook successful.";
        return true;
    }
}

bool Sql::updateBookInfo(EbookInfo bookinfo)
{
    QSqlQuery sql(m_db);
    QString sqlStr = QString("update ebook set author='%1',publisher='%2',publishyear ='%3',"
                             "bookisbn='%4',booklabel='%5',bookintroduction='%6' where bookpath='%7';").
            arg(bookinfo.author).
            arg(bookinfo.publisher).
            arg(bookinfo.publishyear).
            arg(bookinfo.bookisbn).
            arg(bookinfo.booklabel).
            arg(bookinfo.bookintroduction).
            arg(bookinfo.bookpath);
    sql.exec(sqlStr);
    QSqlError err = sql.lastError();
    if (err.type() != QSqlError::NoError)
    {
        qDebug() << "update ebook err:"<<err.text();
        return false;
    }
    else
    {
        qDebug() << "update ebook successful.";
        return true;
    }
}

bool Sql::clearEbook()
{
    QSqlQuery sql(m_db);
    return sql.exec("delete from ebook;");
}

bool Sql::clearNotExistBook(QStringList notExistPathList)
{
    QSqlQuery sql(m_db);
    QString sqlSubStr = "(";  //形成如('E:/dddd.pdf','D:/aaaa.txt')
    for (int i = 0; i < notExistPathList.size(); ++i)
    {
        sqlSubStr.append("'");
        sqlSubStr.append(notExistPathList[i]);
        sqlSubStr.append("'");
        if (i != notExistPathList.size()-1)
        {
            sqlSubStr.append(",");
        }
    }
    sqlSubStr.append(")");
    //形成如delete from ebook where bookpath in ('E:/dddd.pdf','D:/aaaa.txt');
    QString sqlStr = QString("delete from ebook where bookpath in %1;").arg(sqlSubStr);
    qDebug() << "sqlSubStr=" <<sqlSubStr <<"sqlStr="<<sqlStr;
    return sql.exec(sqlStr);
}

bool Sql::clearBookInfoBasisToBookdir(QString bookdir)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from ebook where bookdir = '%1';").arg(bookdir));
}

bool Sql::clearBookInfoBasisToBooktype(QString booktype)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from ebook where booktype = '%1';").arg(booktype));
}

